﻿using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CmccTool.KPI.Services
{
    // 查询数据-异步
    // 基于 Microsoft.Data.Sqlite;
    internal class KpiQueryDbAsync : IDisposable
    {
        private readonly string _connectionString;
        private readonly SqliteConnection _connection;
        private CancellationTokenSource? _cts;

        public KpiQueryDbAsync(string dbPath)
        {
            _connectionString = $"Data Source={dbPath};";
            _connection= new SqliteConnection(_connectionString);
        }

        public async Task<DataTable> ExecuteQueryAsync(string sql)
        {
            // 释放内存
            ReleaseMemory();
            // 取消之前的查询任务（如果存在）
            _cts?.Cancel();
            _cts = new CancellationTokenSource();

            try
            {
                using var connection = new SqliteConnection(_connectionString);
                await connection.OpenAsync(_cts.Token);

                using var command = new SqliteCommand(sql, connection);
               
                using var reader = await command.ExecuteReaderAsync(_cts.Token);
                return await ReadDataTable(reader);
            }
            catch (OperationCanceledException)
            {
                return new DataTable(); // 返回空表表示取消
            }
        }

        private static async Task<DataTable> ReadDataTable(SqliteDataReader reader)
        {
            var table = new DataTable();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                table.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
            }

            while (await reader.ReadAsync())
            {
                var row = table.NewRow();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    row[i] = reader.IsDBNull(i) ? DBNull.Value : reader.GetValue(i);
                }
                table.Rows.Add(row);
            }
            return table;
        }

        public static void ReleaseMemory()
        {
            

            // 清理SQLite内存
            SqliteConnection.ClearAllPools();

            // 建议GC回收（非强制）
            GC.Collect(2, GCCollectionMode.Optimized, false);
        }

        public void CancelQuery() => _cts?.Cancel();

        public void Dispose() => CancelQuery();

    }
}
